package com.hty.accountbook;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

	public static final String DATABASE_NAME = UserActivity.dir + File.separator + "AccountBook.db";
	private final static int VERSION = 2;
	private SQLiteDatabase db;
	private static DBHelper mInstance = null;

	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, VERSION);
		db = getWritableDatabase();
		db.execSQL("CREATE TABLE IF NOT EXISTS users (_id INTEGER PRIMARY KEY, userName TEXT, tableName TEXT)");
		//db.execSQL("ALTER TABLE account RENAME TO account1");//重命名表
        //db.execSQL("DROP TABLE IF EXISTS user");//删除表
	}

	public static synchronized DBHelper getInstance(Context context) {
		if (mInstance == null) {
			mInstance = new DBHelper(context);
		}
		return mInstance;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.e(Thread.currentThread().getStackTrace()[2] + "", "SQLiteOpenHelper.onCreate()");
		this.db = db;
		//db.execSQL("CREATE TABLE "+ tableName + " (_id INTEGER PRIMARY KEY, time INTEGER, type INTEGER, cash REAL, event TEXT)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.e(Thread.currentThread().getStackTrace()[2] + "", "oldVersion: " + oldVersion + ", newVersion: " + newVersion);
		switch (newVersion) {
		case 2:
			break;
		}
	}

	public long insert(String tableName, ContentValues values) {
		db = getWritableDatabase();
		long r = db.insert(tableName, null, values);
		db.close();
		return r;
	}

	public long addUser(ContentValues values){
        db = getWritableDatabase();
        long r = db.insert("users", null, values);
        db.close();
        return r;
    }

	public Cursor getUsers() {
		db = getWritableDatabase();
		Cursor c;
		c = db.query("users", null, null, null, null, null, "_id desc");
		return c;
	}

	public Cursor query(String tableName, String s) {
		db = getWritableDatabase();
		Cursor c;
		if (s.equals("")) {
			c = db.query(tableName, null, null, null, null, null, "time desc");
		} else {
            c = db.query(tableName, null, "event LIKE '%" + s + "%'", null, null, null, "time desc");
        }
		return c;
	}

	public Cursor queryByYearType(String tableName, String year, long type, String s) {
		Log.e(Thread.currentThread().getStackTrace()[2] + "", year + ", " + type + "," + s);
		db = getWritableDatabase();
		String selection;
		if (year.equals("年份")) {
			if (type == -1) {
				selection = "";
			} else {
				selection = "type = " + type;
			}
		} else {
			//long time1 = (Long.valueOf(year)-1900)*365*24*60*60*1000;
			//long time2 = (Long.valueOf(year)-1900+1)*365*24*60*60*1000 - 1;
			//Date date1 = new Date(time1);
			//Date date2 = new Date(time2);
			SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
			SDF.setTimeZone(TimeZone.getTimeZone("GMT+8"));
			String time1 = year + "-01-01 00:00:00:000";
			String time2 = year + "-12-31 23:59:59:999";
			//Log.e(Thread.currentThread().getStackTrace()[2] + "", SDF.format(date1));
			//Log.e(Thread.currentThread().getStackTrace()[2] + "", SDF.format(date2));
			Log.e(Thread.currentThread().getStackTrace()[2] + "", time1);
			Log.e(Thread.currentThread().getStackTrace()[2] + "", time2);
			Date date1 = null, date2 = null;
			try {
				date1 = SDF.parse(time1);
				date2 = SDF.parse(time2);
			} catch (Exception e) {
				Log.e(Thread.currentThread().getStackTrace()[2] + "", e.toString());
			}
			//String selection = "time > " + time1 + " and time < " + time2;
			//String selection = "time between " + time1 + " and " + time2;
			if (type == -1) {
				selection = "time between " + date1.getTime() + " and " + date2.getTime();
			} else {
				selection = "time between " + date1.getTime() + " and " + date2.getTime() + " and type = " + type;
			}
		}
		if (!s.equals("")) {
			if (!selection.equals(""))
				selection = selection + " and ";
			selection = selection + "event LIKE '%" + s + "%'";
		}
		Log.e(Thread.currentThread().getStackTrace()[2] + "", selection);
		Cursor c = db.query(tableName, null, selection, null, null, null, "time desc");
		return c;
	}

	public Cursor timeMin(String tableName) {
		db = getWritableDatabase();
		db.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (_id INTEGER PRIMARY KEY, time INTEGER, type INTEGER, cash REAL, event TEXT)");
		Cursor c = db.rawQuery("select * from " + tableName + " order by time asc limit 1", null);
		//Cursor c = db.query(TableName, null, "time limit 0,0", null, null, null, "time asc");
		return c;
	}

	public Cursor cashTop10(String tableName) {
		db = getWritableDatabase();
		//Cursor c = db.rawQuery("select * from account asc limit 0,9", null);
		Cursor c = db.query(tableName, null, "cash limit 0,9", null, null, null, "cash desc");
		return c;
	}

	public void del(String tableName, String id) {
		if (db == null)
			db = getWritableDatabase();
		db.delete(tableName, "_id=?", new String[] { id });
		// Log.e("id", id + "");
		// db.ExecuteNonQuery(CommandType.Text, "VACUUM");
	}

	@Override
	public void close() {
		if (db != null)
			db.close();
	}

}